from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import relationship,backref

from db_util import Base, Session,engine


class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    uname = Column(String(50), nullable=False, name='name')

    # news = relationship('News')  # 不友好

    def __repr__(self):
        return f'<User: id={self.id} uname={self.uname}>'


# 1对多 ForeignKey的关键字要建立在 多一边
class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(Text, nullable=False)
    uid = Column(Integer, ForeignKey('t_user.id'))

    user = relationship('User', backref='news')  # 将主表的数据注入到这个字段

    def __repr__(self):
        return f'<News: id={self.id} title={self.title} content={self.content} uid={self.uid}>'


def create_data():
    user = User(uname='sxt')
    news1 = News(title='Python', content='flask', uid=1)
    news2 = News(title='MySQL', content='SQL', uid=1)
    user.news.append(news1)
    user.news.append(news2)

    with Session() as ses:
        ses.add(user)
        ses.commit()
    #
    # with Session() as ses:
    #     ses.add(news1)
    #     ses.add(news2)
    #     ses.commit()

def query_data():
    with Session() as ses:
        new1 = ses.query(News).first()
        print(new1)

def query_data2():
    #通过子表查询主表的数据
    with Session() as ses:
        news1 = ses.query(News).first()
        print(news1.user)

def query_data3():
    #通过主表查询子表数据
    with Session() as ses:
        user1 = ses.query(User).first()
        print(user1.news)

if __name__ == '__main__':
    # Base.metadata.drop_all(engine)
    # Base.metadata.create_all(engine)
    create_data()
    # query_data()
    # query_data2()
    # query_data3()